A phone call is considered an international call
when the person calling is in a different country than the person receiving the
call.
What percentage of phone calls are international?
Round the result to 1 decimal.
Assumption:
· The caller_id in phone_info
table refers to both the caller and receiver.
table : phone_calls
table: phone_info
Solution:
select convert(decimal(18,1),100.0 * (sum(case when caller.country_id != receiver.country_id then 1 else 0 end))/count(*),1) as int_calls
from phone_calls as calls
left join phone_info as caller on calls.caller_id = caller.caller_id
left join phone_info as receiver on calls.receiver_id = receiver.caller_id
Output:
SQL Script:
CREATE TABLE [dbo].[phone_calls](
[caller_id]
[int] NULL,
[receiver_id]
[int] NULL,
[call_time]
[datetime] NULL
) ON [PRIMARY]
GO
GO
CREATE TABLE [dbo].[phone_info](
[caller_id]
[int] NULL,
[country_id]
[nvarchar](50) NULL,
[network]
[nvarchar](50) NULL,
[phone_number]
[nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[phone_calls] ([caller_id], [receiver_id], [call_time]) VALUES (1, 2, CAST(N'2022-07-04T10:13:49.000' AS DateTime))
GO
INSERT [dbo].[phone_calls] ([caller_id], [receiver_id], [call_time]) VALUES (1, 5, CAST(N'2022-08-21T23:54:56.000' AS DateTime))
GO
INSERT [dbo].[phone_calls] ([caller_id], [receiver_id], [call_time]) VALUES (5, 1, CAST(N'2022-05-13T17:24:06.000' AS DateTime))
GO
INSERT [dbo].[phone_calls] ([caller_id], [receiver_id], [call_time]) VALUES (5, 6, CAST(N'2022-03-18T12:11:49.000' AS DateTime))
GO
INSERT [dbo].[phone_info] ([caller_id], [country_id], [network], [phone_number]) VALUES (1, N'US', N'Verizon', N'+1-212-897-1964')
GO
INSERT [dbo].[phone_info] ([caller_id], [country_id], [network], [phone_number]) VALUES (2, N'US', N'Verizon', N'+1-703-346-9529')
GO
INSERT [dbo].[phone_info] ([caller_id], [country_id], [network], [phone_number]) VALUES (3, N'US', N'Verizon', N'+1-650-828-4774')
GO
INSERT [dbo].[phone_info] ([caller_id], [country_id], [network], [phone_number]) VALUES (4, N'US', N'Verizon', N'+1-415-224-6663')
GO
INSERT [dbo].[phone_info] ([caller_id], [country_id], [network], [phone_number]) VALUES (5, N'IN', N'Vodafone', N'+91 7503-907302')
GO
INSERT [dbo].[phone_info] ([caller_id], [country_id], [network], [phone_number]) VALUES (6, N'IN', N'Vodafone', N'+91 2287-664895')
GO